﻿//using System;
using System.Data;
using System.Drawing;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;

//using System.Drawing;
//using zkweb;

namespace YG.CommonFuncs
{
    /// <summary>
    /// Excel导出帮助类
    /// </summary>
    public static class Data2XLSX
    {
        /// <summary>
        /// 使用EPPlus导出Excel(xlsx)
        /// </summary>
        /// <param name="sourceTable">数据源</param>
        /// <param name="strFileName">xlsx文件名(不含后缀名)</param>
        public static string DataTableToXLSX(DataTable sourceTable, string strFileName)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName;
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);

                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(sourceTable, true);

                //Format the row
                ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
                Color borderColor = Color.FromArgb(155, 155, 155);

                using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count])
                {
                    rng.Style.Font.Name = "宋体";
                    rng.Style.Font.Size = 10;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));

                    rng.Style.Border.Top.Style = borderStyle;
                    rng.Style.Border.Top.Color.SetColor(borderColor);

                    rng.Style.Border.Bottom.Style = borderStyle;
                    rng.Style.Border.Bottom.Color.SetColor(borderColor);

                    rng.Style.Border.Right.Style = borderStyle;
                    rng.Style.Border.Right.Color.SetColor(borderColor);
                }

                //Format the header row
                using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count])
                {
                    rng.Style.Font.Bold = true;
                    rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
                }

                pck.SaveAs(new FileInfo(strFileName));
                //Write it back to the client
                //HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                //HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));
                //HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;

                //HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
                //HttpContext.Current.Response.End();
            }
            return strFileName;
        }

        /// <summary>
        /// 使用EPPlus导出Excel(xlsx)
        /// </summary>
        /// <param name="sourceTable">数据源</param>
        /// <param name="strFileName">xlsx文件名(不含后缀名)</param>
        /// /// <param name="insamepage">是否导出到同一个sheet中</param>
        public static string DataSetToXLSX(DataSet sourceSet, string strFileName, int insamesheet)
        {
            try
            {
                if (insamesheet == 1)
                {
                    using (ExcelPackage pck = new ExcelPackage())
                    {
                        #region 添加数据

                        int rowindex = 1;
                        //Create the worksheet
                        if (sourceSet.Tables.Count < 1)
                        {
                            return "";
                        }
                        string sheetName = string.IsNullOrEmpty(sourceSet.Tables[0].TableName) ? "Sheet1" : sourceSet.Tables[0].TableName;
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);

                        for (int i = 0; i < sourceSet.Tables.Count; i++)
                        {
                            //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                            ws.Cells["A" + (rowindex).ToString()].LoadFromDataTable(sourceSet.Tables[i], true);

                            //Format the row
                            ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
                            Color borderColor = Color.FromArgb(155, 155, 155);

                            rowindex += sourceSet.Tables[i].Rows.Count;
                            rowindex += 1;//去除标题部分
                            rowindex += 2;//留出两个空格

                            //using (ExcelRange rng = ws.Cells[1, 1, sourceSet.Tables[i].Rows.Count + 1, sourceSet.Tables[i].Columns.Count])
                            //{
                            //    rng.Style.Font.Name = "宋体";
                            //    rng.Style.Font.Size = 10;
                            //    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                            //    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));

                            //    rng.Style.Border.Top.Style = borderStyle;
                            //    rng.Style.Border.Top.Color.SetColor(borderColor);

                            //    rng.Style.Border.Bottom.Style = borderStyle;
                            //    rng.Style.Border.Bottom.Color.SetColor(borderColor);

                            //    rng.Style.Border.Right.Style = borderStyle;
                            //    rng.Style.Border.Right.Color.SetColor(borderColor);
                            //}

                            ////Format the header row
                            //using (ExcelRange rng = ws.Cells[1, 1, 1, sourceSet.Tables[i].Columns.Count])
                            //{
                            //    rng.Style.Font.Bold = true;
                            //    rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            //    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
                            //    rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
                            //}
                        }

                        #endregion 添加数据

                        pck.SaveAs(new FileInfo(strFileName));
                        //Write it back to the client
                        //HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        //HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));
                        //HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;

                        //HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
                        //HttpContext.Current.Response.End();
                    }
                }
                else
                {
                    using (ExcelPackage pck = new ExcelPackage())
                    {
                        #region 添加数据

                        for (int i = 0; i < sourceSet.Tables.Count; i++)
                        {
                            //Create the worksheet
                            string sheetName = string.IsNullOrEmpty(sourceSet.Tables[i].TableName) ? "Sheet1" : sourceSet.Tables[i].TableName;
                            ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);

                            //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                            ws.Cells["A1"].LoadFromDataTable(sourceSet.Tables[i], true);

                            //Format the row
                            ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
                            Color borderColor = Color.FromArgb(155, 155, 155);

                            using (ExcelRange rng = ws.Cells[1, 1, sourceSet.Tables[i].Rows.Count + 1, sourceSet.Tables[i].Columns.Count])
                            {
                                rng.Style.Font.Name = "宋体";
                                rng.Style.Font.Size = 10;
                                rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                                rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));

                                rng.Style.Border.Top.Style = borderStyle;
                                rng.Style.Border.Top.Color.SetColor(borderColor);

                                rng.Style.Border.Bottom.Style = borderStyle;
                                rng.Style.Border.Bottom.Color.SetColor(borderColor);

                                rng.Style.Border.Right.Style = borderStyle;
                                rng.Style.Border.Right.Color.SetColor(borderColor);
                            }

                            //Format the header row
                            using (ExcelRange rng = ws.Cells[1, 1, 1, sourceSet.Tables[i].Columns.Count])
                            {
                                rng.Style.Font.Bold = true;
                                rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
                                rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
                            }
                        }

                        #endregion 添加数据

                        pck.SaveAs(new FileInfo(strFileName));
                        //Write it back to the client
                        //HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        //HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));
                        //HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;

                        //HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
                        //HttpContext.Current.Response.End();
                    }
                }
            }
            catch (System.Exception ex)
            {
                string err = ex.Message;
                //throw;
            }

            return strFileName;
        }
    }
}